﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using EF.Util.DBUtility;
using EF.Model.Base;
using EF.Util;

namespace EF.DAL.Base
{
    public class UserDAO
    {
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="userId">用户Id</param>
        /// <param name="roleId">角色Id</param>
        /// <returns>1,登录成功; 0,登录失败</returns>
        public int Login(string userName, string password, out int userId, out string role, out string realName)
        {
            SqlParameter[] sqlparams = {
                                           new SqlParameter("@userName", SqlDbType.NVarChar, 20),
                                           new SqlParameter("@password", SqlDbType.NVarChar, 10),
                                           new SqlParameter("@userId", SqlDbType.Int, 4),
                                           new SqlParameter("@role", SqlDbType.NVarChar, 50),
                                           new SqlParameter("@realName", SqlDbType.NVarChar, 20),
                                           new SqlParameter("@result", SqlDbType.Int, 4)
                                       };

            sqlparams[0].Value = userName;
            sqlparams[1].Value = password;
            sqlparams[2].Direction = ParameterDirection.Output;
            sqlparams[3].Direction = ParameterDirection.Output;
            sqlparams[4].Direction = ParameterDirection.Output;
            sqlparams[5].Direction = ParameterDirection.Output;

            DbHelperSQL.RunProcedure("spBaseUserLogin", sqlparams);

            userId = (int)sqlparams[2].Value;
            role = sqlparams[3].Value.ToString();
            realName = sqlparams[4].Value.ToString();

            return (int)sqlparams[5].Value;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="spl">查询条件</param>
        /// <param name="lang">语言</param>
        /// <param name="columns">显示的列</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">一页的记录数</param>
        /// <param name="orderBy">排序</param>
        /// <param name="rowsCount">总记录数</param>
        /// <returns></returns>
        public DataTable GetList(List<SqlParameterInfo> spl, string lang, string columns, int? pageIndex, int? pageSize, string orderBy, out int rowsCount)
        {
            SqlParameter[] sqlparams = {
                                           new SqlParameter("@lang", SqlDbType.NVarChar, 10),
                                           new SqlParameter("@columns", SqlDbType.NVarChar, 1000),
                                           new SqlParameter("@pageIndex", SqlDbType.Int, 4),
                                           new SqlParameter("@pageSize", SqlDbType.Int, 4),
                                           new SqlParameter("@orderBy", SqlDbType.NVarChar, 100),
                                           new SqlParameter("@rowsCount", SqlDbType.Int, 4)
                                       };

            sqlparams[0].Value = lang;
            sqlparams[1].Value = columns;
            sqlparams[2].Value = pageIndex;
            sqlparams[3].Value = pageSize;
            sqlparams[4].Value = orderBy;
            sqlparams[5].Direction = ParameterDirection.Output;

            StringBuilder sb = new StringBuilder();
            sb.Append("EXEC spBaseUserList @lang=@lang,@columns=@columns,@pageIndex=@pageIndex,@pageSize=@pageSize,@orderBy=@orderBy,@rowsCount=@rowsCount OUTPUT");

            if (spl != null)
            {
                sb.Append("," + JsonHelp.GetSqlWhereParameter(spl));
            }

            DataTable dt = DbHelperSQL.Query(sb.ToString(), sqlparams).Tables[0];

            rowsCount = (int)sqlparams[5].Value;
            
            return dt;
        }

        /// <summary>
        /// 获取历史用户
        /// </summary>
        /// <param name="lang">语言</param>
        /// <param name="columns">显示的列</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">一页的记录数</param>
        /// <param name="orderBy">排序</param>
        /// <param name="rowsCount">总记录数</param>
        /// <returns></returns>
        public DataTable GetHistory(string lang, string columns, int? pageIndex, int? pageSize, string orderBy, out int rowsCount)
        {
            SqlParameter[] sqlparams = {
                                           new SqlParameter("@lang", SqlDbType.NVarChar, 10),
                                           new SqlParameter("@columns", SqlDbType.NVarChar, 1000),
                                           new SqlParameter("@pageIndex", SqlDbType.Int, 4),
                                           new SqlParameter("@pageSize", SqlDbType.Int, 4),
                                           new SqlParameter("@orderBy", SqlDbType.NVarChar, 100),
                                           new SqlParameter("@rowsCount", SqlDbType.Int, 4)
                                       };

            sqlparams[0].Value = lang;
            sqlparams[1].Value = columns;
            sqlparams[2].Value = pageIndex;
            sqlparams[3].Value = pageSize;
            sqlparams[4].Value = orderBy;
            sqlparams[5].Direction = ParameterDirection.Output;

            SqlDataReader dr = DbHelperSQL.RunProcedure("spBaseUserHistory", sqlparams);
            DataTable dt = new DataTable();
            dt.Load(dr);
            dr.Close();

            rowsCount = (int)sqlparams[5].Value;

            return dt;
        }

        /// <summary>
        /// 恢复被删除的用户
        /// </summary>
        /// <param name="userId">用户Id</param>
        /// <param name="updateUser">更新用户</param>
        /// <param name="lang">语言</param>
        /// <param name="msg">消息</param>
        /// <returns></returns>
        public int Restore(string userIds, int updateUser, string lang, out string msg)
        {
            SqlParameter[] sqlparams = {
                                           new SqlParameter("@userIds", SqlDbType.NVarChar, 300),
                                           new SqlParameter("@updateUser", SqlDbType.Int, 4),
                                           new SqlParameter("@lang", SqlDbType.NVarChar, 10),
                                           new SqlParameter("@result", SqlDbType.Int, 4),
                                           new SqlParameter("@msg", SqlDbType.NVarChar, 300)
                                       };

            sqlparams[0].Value = userIds;
            sqlparams[1].Value = updateUser;
            sqlparams[2].Value = lang;
            sqlparams[3].Direction = ParameterDirection.Output;
            sqlparams[4].Direction = ParameterDirection.Output;

            DbHelperSQL.RunProcedure("spBaseUserRestore", sqlparams);

            msg = sqlparams[4].Value.ToString();

            return (int)sqlparams[3].Value;
        }

        /// <summary>
        /// 获取用户Model
        /// </summary>
        /// <param name="lang">语言</param>
        /// <param name="userId">用户Id</param>
        /// <returns></returns>
        public UserModel GetModel(string lang, int userId)
        {
            SqlParameter[] sqlparams = {
                                           new SqlParameter("@lang", SqlDbType.NVarChar, 10),
                                           new SqlParameter("@userId", SqlDbType.Int)
                                       };

            sqlparams[0].Value = lang;
            sqlparams[1].Value = userId;

            SqlDataReader dr = DbHelperSQL.RunProcedure("spBaseUserModel", sqlparams);

            UserModel model = null;
            
            while (dr.Read())
            {
                model = new UserModel();

                model.UserId = Convert.ToInt32(dr["UserId"].ToString());
                model.UserName = dr["UserName"].ToString();
                model.Password = dr["Password"].ToString();

                if (dr["Role"] != null && dr["Role"].ToString() != "")
                {
                    model.Role = Convert.ToInt32(dr["Role"].ToString());
                }
                
                if (dr["RealName"] != null)
                {
                    model.RealName = dr["RealName"].ToString();
                }
                
                if (dr["Sex"] != null && dr["Sex"].ToString() != "")
                {
                    model.Sex = Convert.ToInt32(dr["Sex"].ToString());
                }
                
                if (dr["IdNumber"] != null)
                {
                    model.IdNumber = dr["IdNumber"].ToString();
                }
                
                if (dr["MobilePhone"] != null)
                {
                    model.MobilePhone = dr["MobilePhone"].ToString();
                }
                
                if (dr["Phone"] != null)
                {
                    model.Phone = dr["Phone"].ToString();
                }
                
                if (dr["SortIndex"] != null && dr["SortIndex"].ToString() != "")
                {
                    model.SortIndex = Convert.ToInt32(dr["SortIndex"].ToString());
                }
                
                if (dr["CreateTime"] != null && dr["CreateTime"].ToString() != "")
                {
                    model.CreateTime = Convert.ToDateTime(dr["CreateTime"].ToString());
                }
                
                if (dr["UpdateTime"] != null && dr["UpdateTime"].ToString() != "")
                {
                    model.UpdateTime = Convert.ToDateTime(dr["UpdateTime"].ToString());
                }
            }

            return model;
        }

        /// <summary>
        /// 用户新建
        /// </summary>
        /// <param name="model">用户Model</param>
        /// <returns>1,新建成功;2,新建失败,用户名已存在;0,新建失败,数据库异常.</returns>
        public int Add(UserModel model, string lang, out string msg)
        {
            SqlParameter[] sqlparams = {
                                         new SqlParameter("@userName", SqlDbType.NVarChar, 20),
                                         new SqlParameter("@password", SqlDbType.NVarChar, 10),
                                         new SqlParameter("@realName", SqlDbType.NVarChar, 20),
                                         new SqlParameter("@sex", SqlDbType.Int, 4),
                                         new SqlParameter("@idNumber", SqlDbType.NVarChar, 30),
                                         new SqlParameter("@mobilePhone", SqlDbType.NVarChar, 50),
                                         new SqlParameter("@phone", SqlDbType.NVarChar, 50),
                                         new SqlParameter("@createUser", SqlDbType.Int, 4),
                                         new SqlParameter("@result", SqlDbType.Int, 4),
                                         new SqlParameter("@lang",SqlDbType.NVarChar, 10),
                                         new SqlParameter("@msg", SqlDbType.NVarChar, 300),
                                         new SqlParameter("@role", SqlDbType.Int, 4)
                                     };

            sqlparams[0].Value = model.UserName;
            sqlparams[1].Value = model.Password;
            sqlparams[2].Value = model.RealName;
            sqlparams[3].Value = model.Sex;
            sqlparams[4].Value = model.IdNumber;
            sqlparams[5].Value = model.MobilePhone;
            sqlparams[6].Value = model.Phone;
            sqlparams[7].Value = model.CreateUser;
            sqlparams[8].Direction = ParameterDirection.Output;
            sqlparams[9].Value = lang;
            sqlparams[10].Direction = ParameterDirection.Output;
            sqlparams[11].Value = model.Role;

            DbHelperSQL.RunProcedure("spBaseUserAdd", sqlparams);

            msg = sqlparams[10].Value.ToString();

            return (int)sqlparams[8].Value;
        }

        /// <summary>
        /// 用户编辑
        /// </summary>
        /// <param name="model">用户Model</param>
        /// <returns>1,编辑成功;0,编辑失败,数据库异常.</returns>
        public int Edit(UserModel model, string lang, out string msg)
        {
            SqlParameter[] sqlparams = {
                                            new SqlParameter("@userId", SqlDbType.Int, 4), 
                                            new SqlParameter("@userName", SqlDbType.NVarChar, 20),
                                            new SqlParameter("@password", SqlDbType.NVarChar, 10),
                                            new SqlParameter("@realName", SqlDbType.NVarChar, 20),
                                            new SqlParameter("@sex", SqlDbType.Int, 4),
                                            new SqlParameter("@idNumber", SqlDbType.NVarChar, 30),
                                            new SqlParameter("@mobilePhone", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@phone", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@updateUser", SqlDbType.Int, 4),
                                            new SqlParameter("@result", SqlDbType.Int, 4),
                                            new SqlParameter("@lang", SqlDbType.NVarChar, 10),
                                            new SqlParameter("@msg", SqlDbType.NVarChar, 300),
                                            new SqlParameter("@role", SqlDbType.Int, 4)
                                       };

            sqlparams[0].Value = model.UserId;
            sqlparams[1].Value = model.UserName;
            sqlparams[2].Value = model.Password;
            sqlparams[3].Value = model.RealName;
            sqlparams[4].Value = model.Sex;
            sqlparams[5].Value = model.IdNumber;
            sqlparams[6].Value = model.MobilePhone;
            sqlparams[7].Value = model.Phone;
            sqlparams[8].Value = model.UpdateUser;
            sqlparams[9].Direction = ParameterDirection.Output;
            sqlparams[10].Value = lang;
            sqlparams[11].Direction = ParameterDirection.Output;
            sqlparams[12].Value = model.Role;

            DbHelperSQL.RunProcedure("spBaseUserEdit", sqlparams);

            msg = sqlparams[11].Value.ToString();

            return (int)sqlparams[9].Value;
        }

        /// <summary>
        /// 用户逻辑删除
        /// </summary>
        /// <param name="userId">被逻辑删除用户Id</param>
        /// <param name="updateUser">执行逻辑删除用户Id</param>
        /// <returns>1,逻辑删除成功;0,逻辑删除失败,数据库异常.</returns>
        public int LogicDel(string userIds, int updateUser, string lang, out string msg)
        {
            SqlParameter[] sqlparams = {
                                           new SqlParameter("@userIds", SqlDbType.NVarChar, 100),
                                           new SqlParameter("@updateUser", SqlDbType.Int, 4),
                                           new SqlParameter("@result", SqlDbType.Int, 4),
                                           new SqlParameter("@lang", SqlDbType.NVarChar, 10),
                                           new SqlParameter("@msg", SqlDbType.NVarChar, 300)
                                       };

            sqlparams[0].Value = userIds;
            sqlparams[1].Value = updateUser;
            sqlparams[2].Direction = ParameterDirection.Output;
            sqlparams[3].Value = lang;
            sqlparams[4].Direction = ParameterDirection.Output;

            DbHelperSQL.RunProcedure("spBaseUserLogicDel", sqlparams);

            msg = sqlparams[4].Value.ToString();

            return (int)sqlparams[2].Value;
        }
    }
}
